﻿using Adp;
using Microsoft.AspNetCore.Mvc;
using Yqd.Models;
using Yqd.Controllers.Filter;
using Adp.Common;
using System.Linq;
using System;
using Yqd.Core;
using System.Collections.Generic;
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.Style;
using Newtonsoft.Json.Linq;
using Yqd.Enm;
using Newtonsoft.Json;

namespace Yqd.Controllers
{
    [Area("Yqd")]
    public class WxExactSuperviseController : Controller
    {
        YqdContext ctx;
        DdService ddService;

        public WxExactSuperviseController(YqdContext ctx, DdService ddService)
        {
            this.ctx = ctx;
            this.ddService = ddService;
        }

        [User]
        public ActionResult GetList()
        {
            var SuperviseID = Form.GetLong("SuperviseID");
            var supervise = ctx.Yqd_ExactSupervise.FirstOrDefault(it => it.SuperviseID == SuperviseID);
            if (supervise == null)
            {
                return this.ToJson(new
                {
                    success = true,
                    data = new List<object>()
                });
            }
            var scheme = ctx.Yqd_Scheme.Where(it => !it.IsDel && it.ItemID == SuperviseID && it.ItemType == 事项类型.精准督查).ToList();
            var l = WxUser.LogonInfo;
            var DeptRole = l.Role.Any(it => it == 角色类型.管理员 || it == 角色类型.部门管理员);
            var MsRole = l.Role.Any(it => it == 角色类型.管理员 || it == 角色类型.督查人员);
            var plan = ctx.Yqd_JobPlan.Where(it => it.ItemID == SuperviseID && it.ItemType == 事项类型.精准督查).ToList();
            var progress = ctx.Yqd_PlanProgress.Where(it => it.ItemID == SuperviseID && it.ItemType == 事项类型.精准督查).ToList();
            var dept = ctx.Yqd_Unit.Select(it => new { it.UnitCode, it.Name }).ToList();
            var task = ctx.Yqd_Task.FirstOrDefault(it => it.TaskID == supervise.TaskID);
            string TaskName = "", LeaderUnitCode = "";
            if (task != null)
            {
                TaskName = task.TaskName;
                LeaderUnitCode = task.LeaderUnitCode;
            }
            var user = ctx.Yqd_User.Select(it => new { it.EmployeeCode, it.Name }).ToList();
            var data = new List<dynamic>();
            var sm = scheme.Where(it => it.CheckStatus != 审核状态.退回);
            var pg = progress.Where(it => it.CheckStatus != 审核状态.退回);
            var a = ctx.Yqd_Assign.FirstOrDefault(it => it.TaskID == supervise.TaskID && it.ItemID == SuperviseID && it.ItemType == 事项类型.精准督查);
            object Assign = null;
            var IsLeadUnit = false;
            if (a != null)
            {
                List<string> listCooperativeUnit = new List<string>();
                if (!string.IsNullOrWhiteSpace(a.CooperativeUnit))
                {
                    listCooperativeUnit = JsonConvert.DeserializeObject<List<string>>(a.CooperativeUnit);
                }

                IsLeadUnit = a.LeadUnit == l.UnitCode;
                Assign = new
                {
                    a.AssignID,
                    Unit = dept.FirstOrDefault(it => it.UnitCode == a.UnitCode)?.Name,
                    UnitCode = a.LeadUnit,
                    LeadUnit = dept.FirstOrDefault(it => it.UnitCode == a.LeadUnit)?.Name,
                    CooperativeUnit = string.IsNullOrWhiteSpace(a.CooperativeUnit) ? "" : string.Join(",", dept.Where(it => listCooperativeUnit.Contains(it.UnitCode)).Select(it => it.Name)),
                    CompleteLimit = a.CompleteLimit.ToString("yyyy-MM-dd"),
                    ReportLimit = a.ReportLimit.ToString("yyyy-MM-dd"),
                    FeedbackLoop = a.FeedbackLoop == 进展反馈周期.自定义 ? a.Days + "天" : a.FeedbackLoop.ToString(),
                    FeedbackLoopId = (int)a.FeedbackLoop,
                    ProgressInfo = a.ProgressInfo ?? "",
                    a.Days,
                    Material = !string.IsNullOrWhiteSpace(a.Material) ? JArray.Parse(a.Material) : new JArray(),
                    ReportTime = a.ReportTime?.ToString("yyyy-MM-dd") ?? "",
                    Instime = a.Instime.ToString("yyyy-MM-dd")
                };
            }
            var ap = ctx.Yqd_SchemeApproval.Where(it => it.ItemID == SuperviseID && it.ItemType == 事项类型.精准督查).OrderByDescending(x => x.ApprovalID).FirstOrDefault();
            object SchemeApproval = null;
            if (ap != null)
            {
                SchemeApproval = new
                {
                    Leader = ap.Leader ?? "",
                    Dept = ap.Dept ?? "",
                    DeptLeader = ap.DeptLeader ?? "",
                    Attachment = !string.IsNullOrWhiteSpace(ap.Attachment) ? JsonConvert.DeserializeObject(ap.Attachment) : new object()
                };
            }

            return this.ToJson(new
            {
                success = true,
                DeptRole,
                MsRole,
                IsLeadUnit,
                TaskName,
                LeaderUnitCode,
                supervise.SuperviseID,
                supervise.LightSpot,
                supervise.Problem,
                supervise.IsLb,
                Photos = !string.IsNullOrWhiteSpace(supervise.Photos) ? JArray.Parse(supervise.Photos) : new JArray(),
                Status = supervise.Status.ToString().Replace("推进", ""),
                Assign,
                SchemeApproval,
                ShowSchemeHistory = scheme.Any(s => s.CheckStatus == 审核状态.退回),
                ShowScheme = DeptRole && Assign != null && (scheme.Count == 0 || scheme.Any(s => s.IsComplete)),
                Scheme = sm.OrderBy(it => it.Instime).Select(n => new
                {
                    n.SchemeID,
                    JobTarget = n.JobTarget.Replace("\r\n", "<br/>"),
                    JobType = n.JobType.ToString(),
                    IsCheck = n.CheckStatus == Enm.审核状态.待审,
                    Plan = plan.Where(d => d.SchemeID == n.SchemeID).OrderBy(it => it.CompleteTime).Select(s => new
                    {
                        s.PlanID,
                        Measure = s.Measure ?? "",
                        s.Instime,
                        CompleteTime = s.CompleteTime.ToString("yyyy-MM-dd"),
                        CooperativeUnit = string.IsNullOrWhiteSpace(s.CooperativeUnit) ? "" : string.Join(",", dept.Where(it => ("," + s.CooperativeUnit + ",").Contains("," + it.UnitCode + ",")).Select(it => it.Name)),
                        PlanStatus = s.PlanStatus.ToString(),
                        s.FlowIsOver,
                        s.TimeLine,
                        ShowHistory = progress.Any(b => b.PlanID == s.PlanID && b.CheckStatus == 审核状态.退回),
                        LoopDate = string.IsNullOrWhiteSpace(s.TimeLine) || s.PlanStatus == 工作计划状态.已完成 ? new List<object>() : s.TimeLine.Split(",").Where(ld => !pg.Any(b => b.PlanID == s.PlanID && b.FeedbackTime == DateTime.Parse(ld) && (b.ProgressStatus == 0 || b.ProgressStatus == 任务状态.已完成))).Select(d => new { Name = d, Value = d }).ToList<object>(),//|| b.ProgressStatus == 任务状态.正常推进
                        Progress = pg.Where(b => b.PlanID == s.PlanID).Select(p => new
                        {
                            p.ProgressID,
                            IsCheck = p.AuditStatus == 0,
                            ProgressInfo = p.ProgressInfo + (p.ProgressStatus != 任务状态.退回 ? " (" + p.ProgressStatus.ToString().Replace("推进", "") + ")" : ""),
                            ProgressStatus = p.ProgressStatus.ToString().Replace("推进", ""),
                            ProofMaterial = !string.IsNullOrWhiteSpace(p.ProofMaterial) ? JArray.Parse(p.ProofMaterial) : new JArray(),
                            p.Problem,
                            p.Experience,
                            p.CompleteCount,
                            p.CompletingCount,
                            fbTime = p.FeedbackTime.ToString("yyyy-MM-dd"),
                            ExperienceMaterial = !string.IsNullOrWhiteSpace(p.ExperienceMaterial) ? JArray.Parse(p.ExperienceMaterial) : new JArray(),
                            Feedbacker = user.FirstOrDefault(u => u.EmployeeCode == p.EmployeeCode)?.Name,
                            FeedbackInstime = p.Instime.ToString("yyyy-MM-dd HH;mm:ss"),
                            p.Instime
                        })
                    })
                })
            });
        }

        /// <summary>
        /// 10：工作方案审核；20：进展反馈审核；30：办结报告审核
        /// </summary>
        /// <returns></returns>
        [User]
        public ActionResult GeteSuperviseList()
        {
            var TaskID = Form.GetLong("TaskID");
            var year = Form.GetInt("Year", 0);
            var Guid = Form.GetString("GUID");
            var Status = Form.GetInt("Status", 0);
            //var Lb = Form.GetInt("Lb", 0);
            var sql = " IsDel=0 and [TaskID]={0}";
            if (Guid.HasValue()) sql += " and [GUID]={1}";
            if (year > 0) sql += " and year([Instime])={2}";
            var page = Form.GetInt("page", 1);
            var pagesize = Form.GetInt("pagesize", int.MaxValue);
            if (pagesize == 0) pagesize = int.MaxValue;
            //sql += " and [islb]=" + Lb;
            switch (Status)
            {
                case 10:
                    sql += " and [TaskID] in(select TaskID from Yqd_Scheme where IsDel=0 and TaskID={0} and ItemType=" + (int)事项类型.精准督查 + ")";
                    break;
                case 20:
                    sql += " and [TaskID] in(select TaskID from Yqd_PlanProgress where isDel=0 and TaskID={0} and ItemType=" + (int)事项类型.精准督查 + ")";
                    break;
                case 30:
                    sql += " and [TaskID] in(select TaskID from Yqd_FinishReport where isDel=0 and TaskID={0} and ItemType=" + (int)事项类型.精准督查 + ")";
                    break;
            }
            var platform = Form.GetInt("platform", 1);
            if (platform == 2)
            {
                var l = WxUser.LogonInfo;
                var sIDs = ctx.Yqd_Assign.Where(it => it.TaskID == TaskID && it.ItemType == 事项类型.精准督查 && (it.LeadUnit == l.UnitCode || ("," + it.CooperativeUnit + ",").Contains("," + l.UnitCode + ","))).Select(it => it.ItemID).ToArray();
                sql += " and [SuperviseID] in(" + string.Join(",", sIDs) + ")";
            }
            var (totalcount, rs) = ctx.Database.SqlPager<Yqd_ExactSupervise>((page - 1) * pagesize, pagesize, "*", sql, "[Instime] asc", TaskID, Guid, year);
            var data = rs.Select(it => new
            {
                it.SuperviseID,
                it.LightSpot,
                it.Problem,
                it.IsLb,
                Photos = !string.IsNullOrWhiteSpace(it.Photos) ? JArray.Parse(it.Photos) : new JArray(),
                Status = it.Status.ToString()
            });
            var task = ctx.Yqd_Task.FirstOrDefault(it => it.TaskID == TaskID);
            var TaskName = "";
            if (task != null) TaskName = task.TaskName;
            return this.ToJson(new
            {
                success = true,
                totalcount,
                TaskName,
                sql,
                data = data.Where(it => !it.IsLb),
                lbdata = data.Where(it => it.IsLb)
            });
        }

        [User]
        public ActionResult GetWeeklyPaper()
        {
            var sdate = Form.GetDateTime("sdate", GetWeekFirstDay());
            var edate = Form.GetDateTime("edate", GetWeekLastDay());
            var tasks = ctx.Yqd_Task.Select(it => new { it.TaskID, it.TaskName }).ToList();
            var ed = edate.AddDays(1);
            var assign = ctx.Yqd_Assign.Where(it => it.ItemType == 事项类型.精准督查 && it.Instime >= sdate && it.Instime < ed);
            var mIDs = ctx.Yqd_Scheme.Where(it => !it.IsDel && it.CheckStatus == 审核状态.同意 && it.ItemType == 事项类型.精准督查).Select(it => it.SchemeID).ToArray();
            var unit = ctx.Yqd_Unit.Select(it => new { it.UnitCode, it.Name });
            var fb = ctx.Yqd_JobPlan.Where(it => mIDs.Contains(it.SchemeID) && it.ItemType == 事项类型.精准督查).Select(it => new { it.ItemID, it.Measure }).ToList();
            var sIDs = assign.Select(it => it.ItemID).ToArray();
            var ds = ctx.Database.SqlQuery<Yqd_ExactSupervise>(string.Format("select * from Yqd_ExactSupervise where isDel=0 and SuperviseID in('{0}') order by Instime asc", string.Join("','", sIDs)));
            var data = new List<object>();
            foreach (var s in ds)
            {
                var a = assign.FirstOrDefault(it => it.ItemID == s.SuperviseID);
                if (a != null)
                {
                    data.Add(new
                    {
                        TaskID = s.TaskID.ToString(),
                        tasks.FirstOrDefault(it => it.TaskID == s.TaskID)?.TaskName,
                        LeadUnit = unit.FirstOrDefault(it => it.UnitCode == a.LeadUnit)?.Name,
                        s.Problem,
                        Feedback = string.Join("<br/>", fb.Where(it => it.ItemID == s.SuperviseID).Select(it => it.Measure)),
                        AssignTime = a.Instime.ToString("yyyy年MM月dd日"),
                        AssignLimit = a.CompleteLimit.ToString("yyyy年MM月dd日")
                    });
                }
            }
            return this.ToJson(new
            {
                success = true,
                sdate = sdate.ToString("yyyy-MM-dd"),
                edate = edate.ToString("yyyy-MM-dd"),
                totalcount = data.Count(),
                data
            });
        }

        /// <summary>
        /// 得到本周第一天
        /// </summary>
        public DateTime GetWeekFirstDay()
        {
            var dt = DateTime.Now.Date;
            int weeknow = Convert.ToInt32(dt.DayOfWeek);
            weeknow = (weeknow == 0 ? (7 - 1) : (weeknow - 1));
            int daydiff = (-1) * weeknow;
            string FirstDay = dt.AddDays(daydiff).ToString("yyyy-MM-dd");
            return Convert.ToDateTime(FirstDay);
        }

        /// <summary>
        /// 得到本周最后一天(以星期天为最后一天)
        /// </summary>
        public DateTime GetWeekLastDay()
        {
            var dt = DateTime.Now.Date;
            int weeknow = Convert.ToInt32(dt.DayOfWeek);
            weeknow = (weeknow == 0 ? 7 : weeknow);
            int daydiff = (7 - weeknow);
            string LastDay = dt.AddDays(daydiff).ToString("yyyy-MM-dd");
            return Convert.ToDateTime(LastDay);
        }

        [User]
        public ActionResult GetUnTaskCount()
        {
            var TaskType = Form.GetString("TaskType", "");
            var isLb = Form.GetInt("isLb", 0);
            var year = Form.GetInt("Year");
            string sql = "";
            if (year > 0)
            {
                sql += " and year(Instime)=" + year;
            }
            int SchemeCount = 0;
            int ProgressCount = 0;
            int ReportCount = 0;
            string where = "";
            if (TaskType != "")
            {
                where += " and (','+[TaskType]+',') like '%," + TaskType + ",%'";
            }

            SchemeCount = ctx.Database.SqlQuery<int>("select count(distinct TaskID) from Yqd_ExactSupervise where isDel=0  and Status in (2,3,4) and [SuperviseID] in(select ItemID from Yqd_Scheme where isDel=0 " + sql + " and ItemType={0} and [CheckStatus]={1} and TaskID in (select TaskID from Yqd_Task where IsShow=1 " + where + ")) and islb={2}", (int)事项类型.精准督查, (int)审核状态.待审, isLb).First();
            ProgressCount = ctx.Database.SqlQuery<int>("select count(distinct TaskID) from Yqd_ExactSupervise where isDel=0  and Status in (2,3,4) and [SuperviseID] in(select ItemID from Yqd_PlanProgress where isDel=0 " + sql + " and ItemType={0} and [CheckStatus]={1} and AuditStatus=0 and TaskID in (select TaskID from Yqd_Task where IsShow=1 " + where + ")) and islb={2}", (int)事项类型.精准督查, (int)审核状态.待审, isLb).First();
            ReportCount = ctx.Database.SqlQuery<int>("select count(distinct TaskID) from Yqd_ExactSupervise where isDel=0  and [SuperviseID] in(select ItemID from Yqd_FinishReport where isDel=0 " + sql + "  and ItemType={0} and [CheckStatus]={1} and TaskID in (select TaskID from Yqd_Task where IsShow=1 " + where + ")) and islb={2}", (int)事项类型.精准督查, (int)审核状态.待审, isLb).First();

            int waitSchemeCount = ctx.Database.SqlQuery<int>("select count(distinct TaskID) from Yqd_ExactSupervise where isDel=0  and Status in (2,3,4) and [SuperviseID] in(select ItemID from Yqd_Scheme where isDel=0 " + sql + "  and ItemType={0} and [CheckStatus]={1} and TaskID in (select TaskID from Yqd_Task where IsShow=1 " + where + ")) and islb={2}", (int)事项类型.精准督查, (int)审核状态.待审, isLb).First();
            int waitProgressCount = ctx.Database.SqlQuery<int>("select count(distinct TaskID) from Yqd_ExactSupervise where isDel=0  and Status in (2,3,4) and [SuperviseID] in(select ItemID from Yqd_PlanProgress where isDel=0 " + sql + "  and ItemType={0} and [CheckStatus]={1} and AuditStatus=0 and TaskID in (select TaskID from Yqd_Task where IsShow=1 " + where + ")) and islb={2}", (int)事项类型.精准督查, (int)审核状态.待审, isLb).First();
            int waitReportCount = ctx.Database.SqlQuery<int>("select count(distinct TaskID) from Yqd_ExactSupervise where isDel=0  and [SuperviseID] in(select ItemID from Yqd_FinishReport where isDel=0 " + sql + "  and ItemType={0} and [CheckStatus]={1} and TaskID in (select TaskID from Yqd_Task where IsShow=1 " + where + ")) and islb={2}", (int)事项类型.精准督查, (int)审核状态.待审, isLb).First();

            return this.ToJson(new
            {
                success = true,
                SchemeCount,
                ProgressCount,
                ReportCount,
                waitSchemeCount,
                waitProgressCount,
                waitReportCount
            });
        }

        [User]
        public ActionResult OutImport()
        {
            try
            {
                var sdate = Form.GetDateTime("sdate", GetWeekFirstDay());
                var edate = Form.GetDateTime("edate", GetWeekLastDay());
                var tasks = ctx.Yqd_Task.Select(it => new { it.TaskID, it.TaskName }).ToList();
                var ed = edate.AddDays(1);
                var dt = sdate.ToString("yyyy年M月d日");
                if (sdate.Year == edate.Year)
                    dt += edate.ToString("-M月d日");
                else
                    dt += edate.ToString("-yyyy年M月d日");
                var assign = ctx.Yqd_Assign.Where(it => it.ItemType == 事项类型.精准督查 && it.Instime >= sdate && it.Instime < ed);
                var mIDs = ctx.Yqd_Scheme.Where(it => !it.IsDel && it.CheckStatus == 审核状态.同意 && it.ItemType == 事项类型.精准督查).Select(it => it.SchemeID).ToArray();
                var unit = ctx.Yqd_Unit.Select(it => new { it.UnitCode, it.Name });
                var fb = ctx.Yqd_JobPlan.Where(it => mIDs.Contains(it.SchemeID) && it.ItemType == 事项类型.精准督查).Select(it => new { it.ItemID, it.Measure }).ToList();
                var sIDs = assign.Select(it => it.ItemID).ToArray();
                var ds = ctx.Database.SqlQuery<Yqd_ExactSupervise>(string.Format("select * from Yqd_ExactSupervise where isDel=0 and SuperviseID in('{0}') order by Instime asc", string.Join("','", sIDs)));
                var data = new List<dynamic>();
                foreach (var s in ds)
                {
                    var a = assign.FirstOrDefault(it => it.ItemID == s.SuperviseID);
                    if (a != null)
                    {
                        data.Add(new
                        {
                            tasks.FirstOrDefault(it => it.TaskID == s.TaskID)?.TaskName,
                            LeadUnit = unit.FirstOrDefault(it => it.UnitCode == a.LeadUnit)?.Name,
                            s.Problem,
                            Feedback = string.Join("\n", fb.Where(it => it.ItemID == s.SuperviseID).Select(it => it.Measure)),
                            AssignTime = a.Instime.ToString("yyyy年MM月dd日"),
                            AssignLimit = a.CompleteLimit.ToString("yyyy年MM月dd日")
                        });
                    }
                }
                const string filename = "督查周报表.xlsx";
                var path = Config.Yqdpath("/sys/xls/");
                if (!Directory.Exists(path)) Directory.CreateDirectory(path);
                FileInfo file = new FileInfo(path + filename);
                if (file.Exists) file.Delete();
                var title = new[] { "任务名称", "责任单位", "督查问题", "办理情况", "交办时间", "办理时限", "备注" };
                using (ExcelPackage package = new ExcelPackage(file))
                {
                    ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("督查周报");

                    worksheet.Cells[1, 1].Style.Font.Name = "宋体";
                    worksheet.Cells[1, 1].Style.Font.Bold = true;
                    worksheet.Cells[1, 1].Style.Font.Size = 16;
                    worksheet.Cells.Style.ShrinkToFit = true;
                    worksheet.Row(1).Height = 80;
                    worksheet.Cells[1, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                    worksheet.Cells[1, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    worksheet.Cells[1, 1].Value = "督查周报\n" + dt;
                    worksheet.Cells[1, 1, 1, 7].Merge = true;
                    int index = 1;
                    foreach (string t in title)
                    {
                        worksheet.Cells[2, index].Style.Font.Name = "宋体";
                        worksheet.Cells[2, index].Style.Font.Bold = true;
                        worksheet.Cells[2, index].Style.Font.Size = 12;
                        worksheet.Cells.Style.ShrinkToFit = true;
                        worksheet.Row(2).Height = 30;
                        worksheet.Column(index).Width = 20;
                        worksheet.Row(2).CustomHeight = true;
                        worksheet.Cells[2, index].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        worksheet.Cells[2, index].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                        worksheet.Cells[2, index].Value = t;
                        index++;
                    }
                    index = 3;
                    foreach (var d in data)
                    {
                        worksheet.Cells[index, 1].Style.Font.Name = "宋体";
                        worksheet.Cells[index, 1].Style.Font.Bold = false;
                        worksheet.Cells[index, 1].Style.Font.Size = 12;
                        worksheet.Cells.Style.ShrinkToFit = true;
                        worksheet.Row(index).Height = 30;
                        worksheet.Column(1).Width = 60;
                        worksheet.Row(index).CustomHeight = true;
                        worksheet.Cells[index, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        worksheet.Cells[index, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                        worksheet.Cells[index, 1].Value = d.TaskName;
                        worksheet.Cells[index, 2].Style.Font.Name = "宋体";
                        worksheet.Cells[index, 2].Style.Font.Bold = false;
                        worksheet.Cells[index, 2].Style.Font.Size = 12;
                        worksheet.Cells.Style.ShrinkToFit = true;
                        worksheet.Row(index).Height = 30;
                        worksheet.Column(2).Width = 20;
                        worksheet.Row(index).CustomHeight = true;
                        worksheet.Cells[index, 2].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        worksheet.Cells[index, 2].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                        worksheet.Cells[index, 2].Value = d.LeadUnit;
                        worksheet.Cells[index, 3].Style.Font.Name = "宋体";
                        worksheet.Cells[index, 3].Style.Font.Bold = false;
                        worksheet.Cells[index, 3].Style.Font.Size = 12;
                        worksheet.Cells.Style.ShrinkToFit = true;
                        worksheet.Row(index).Height = 30;
                        worksheet.Column(3).Width = 60;
                        worksheet.Row(index).CustomHeight = true;
                        worksheet.Cells[index, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        worksheet.Cells[index, 3].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                        worksheet.Cells[index, 3].Value = d.Problem;
                        worksheet.Cells[index, 4].Style.Font.Name = "宋体";
                        worksheet.Cells[index, 4].Style.Font.Bold = false;
                        worksheet.Cells[index, 4].Style.Font.Size = 12;
                        worksheet.Cells.Style.ShrinkToFit = true;
                        worksheet.Row(index).Height = 30;
                        worksheet.Column(4).Width = 60;
                        worksheet.Row(index).CustomHeight = true;
                        worksheet.Cells[index, 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        worksheet.Cells[index, 4].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                        worksheet.Cells[index, 4].Value = d.Feedback;
                        worksheet.Cells[index, 5].Style.Font.Name = "宋体";
                        worksheet.Cells[index, 5].Style.Font.Bold = false;
                        worksheet.Cells[index, 5].Style.Font.Size = 12;
                        worksheet.Cells.Style.ShrinkToFit = true;
                        worksheet.Row(index).Height = 30;
                        worksheet.Column(5).Width = 20;
                        worksheet.Row(index).CustomHeight = true;
                        worksheet.Cells[index, 5].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        worksheet.Cells[index, 5].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                        worksheet.Cells[index, 5].Value = d.AssignTime;
                        worksheet.Cells[index, 6].Style.Font.Name = "宋体";
                        worksheet.Cells[index, 6].Style.Font.Bold = false;
                        worksheet.Cells[index, 6].Style.Font.Size = 12;
                        worksheet.Cells.Style.ShrinkToFit = true;
                        worksheet.Row(index).Height = 30;
                        worksheet.Column(6).Width = 20;
                        worksheet.Row(index).CustomHeight = true;
                        worksheet.Cells[index, 6].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        worksheet.Cells[index, 6].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                        worksheet.Cells[index, 6].Value = d.AssignLimit;
                        worksheet.Cells[index, 7].Style.Font.Name = "宋体";
                        worksheet.Cells[index, 7].Style.Font.Bold = false;
                        worksheet.Cells[index, 7].Style.Font.Size = 12;
                        worksheet.Cells.Style.ShrinkToFit = true;
                        worksheet.Row(index).Height = 30;
                        worksheet.Column(7).Width = 30;
                        worksheet.Row(index).CustomHeight = true;
                        worksheet.Cells[index, 7].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        worksheet.Cells[index, 7].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                        worksheet.Cells[index, 7].Value = "";
                        index++;
                    }
                    package.Save();
                    package.Dispose();
                    return Json(new
                    {
                        success = true,
                        filepath = "/sys/xls/" + filename
                    });
                }
            }
            catch (Exception ex)
            {
                return Json(new
                {
                    success = false,
                    ex.Message
                });
            }
        }
        /// <summary>
        /// 更新督查
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        [User]
        public IActionResult UpdateSupervise(Yqd_ExactSupervise model)
        {
            var assign = ctx.Yqd_ExactSupervise.Where(x => x.SuperviseID == model.SuperviseID && x.TaskID == model.TaskID).SingleOrDefault();
            if (assign == null)
            {
                return this.ToJson(new
                {
                    success = false,
                    ret = 0,
                    msg = "交办任务不存在"
                });
            }
            assign.Status = model.Status;
            var ret = ctx.SaveChanges();
            if (ret > 0)
            {
            }
            return this.ToJson(new
            {
                success = true,
                ret
            });
        }

        /// <summary>
        /// 添加督查取证
        /// </summary>
        /// <returns></returns>
        [User]
        public IActionResult Add()
        {
            var TaskID = Form.GetLong("TaskID");
            var IsLb = Form.GetBool("IsLb", false);
            var IsJb = Form.GetBool("IsJb", true);
            var IsReport = Form.GetInt("IsReport", 0);
            var datas = Form.GetString("datas").ToJArray();
            var guid = Guid.NewGuid().ToString();
            var lst = new List<Yqd_ExactSupervise>();
            foreach (var info in datas)
            {
                lst.Add(new Yqd_ExactSupervise
                {
                    TaskID = TaskID,
                    LightSpot = info["LightSpot"].ToString(),
                    Problem = info["Problem"].ToString(),
                    Photos = info["Photos"]?.ToString() ?? "",
                    Instime = DateTime.Now,
                    Status = 任务状态.正常推进,
                    IsLb = IsLb,
                    GUID = guid,
                    EmployeeCode = WxUser.LogonInfo.EmployeeCode,
                    UnitCode = WxUser.LogonInfo.UnitCode,
                    IsReport = IsReport
                });
            }
            ctx.Yqd_ExactSupervise.AddRange(lst);
            var ret = ctx.SaveChanges();
            if (ret > 0)
            {
                if (IsJb)
                {
                    string leadUnit = "";
                    var al = new List<Yqd_Assign>();
                    foreach (var info in datas)
                    {
                        var SuperviseID = 0;
                        var problem = info["Problem"].ToString();
                        var m = lst.FirstOrDefault(it => it.Problem == problem);
                        if (m != null) SuperviseID = m.SuperviseID;
                        var loop = int.Parse(info["FeedbackLoop"].ToString());
                        al.Add(new Yqd_Assign
                        {
                            TaskID = TaskID,
                            ItemID = SuperviseID,
                            ItemType = 事项类型.精准督查,
                            LeadUnit = info["LeadUnit"].ToString(),
                            CooperativeUnit = info["CooperativeUnit"]?.ToString() ?? "",
                            ReportLimit = DateTime.Parse(info["ReportLimit"].ToString()),
                            CompleteLimit = DateTime.Parse(info["CompleteLimit"].ToString()),
                            FeedbackLoop = (进展反馈周期)loop,
                            Days = int.Parse(info["Days"].ToString()),
                            Instime = DateTime.Now,
                            EmployeeCode = WxUser.LogonInfo.EmployeeCode,
                            UnitCode = WxUser.LogonInfo.UnitCode,
                            IsReport = IsReport
                        });
                        leadUnit = info["LeadUnit"].ToString();
                    }
                    ctx.Yqd_Assign.AddRange(al);
                    ret = ctx.SaveChanges();
                    if (ret > 0)
                    {
                        var nt = DateTime.Now.Date;
                        foreach (var m in lst)
                        {
                            var assign = al.FirstOrDefault(it => it.ItemID == m.SuperviseID);
                            if (assign != null)
                            {
                                var task = ctx.Yqd_Task.FirstOrDefault(it => it.TaskID == TaskID);
                                if (task != null)
                                {
                                    //关闭通知
                                    //推送至主办单位
                                    var msg = ddService.Notification(new List<string>(), "精准督查", task.TaskID.ToString(), "任务名称:" + task.TaskName, $"{WxUser.LogonInfo.UnitName}({WxUser.LogonInfo.Name})现对该任务督查发现的问题予以交办，请及时办理！", pushStep: 推送流程.交办, unitCode: leadUnit);
                                    Logs.Warning<WxMySuperviseController>(WxUser.LogonInfo.Name + "发了精准督查通知，返回值：" + msg);
                                }
                                TaskLogService.AddLog(TaskID, "填写了责任清单和时间清单");
                            }
                        }
                    }
                }
                TaskLogService.AddLog(TaskID, "精准督查取证了");
            }
            return this.ToJson(new
            {
                success = true,
                ret
            });
        }

        [User]
        public ActionResult Delete()
        {
            var l = WxUser.LogonInfo;
            if (!l.Role.Contains(角色类型.管理员))
            {
                return this.ToJson(new
                {
                    success = false,
                    msg = "无权限删除"
                });
            }
            var SuperviseID = Form.GetInt("SuperviseID");
            var ret = ctx.Database.ExecuteSql("update Yqd_ExactSupervise set IsDel=1 where SuperviseID={0};", SuperviseID);
            return this.ToJson(new
            {
                success = true,
                ret
            });
        }

        [User]
        public ActionResult GetSuperviseList()
        {
            var TaskID = Form.GetLong("TaskID");
            var Lb = Form.GetBool("Lb", false);
            var task = ctx.Yqd_Task.FirstOrDefault(it => it.TaskID == TaskID);
            string TaskName = "", LeaderUnitCode = "";
            if (task != null)
            {
                TaskName = task.TaskName;
                LeaderUnitCode = task.LeaderUnitCode;
            }
            var r = ctx.Database.SqlQuery<Yqd_ExactSupervise>("select * from Yqd_ExactSupervise where IsDel=0 and [TaskID]={0} and IsLb={2} and [SuperviseID] not in(select ItemID from yqd_Assign where ItemType={1}) order by [Instime] desc", TaskID, (int)事项类型.精准督查, Lb ? 1 : 0);
            return this.ToJson(new
            {
                success = true,
                totalcount = r.Count(),
                TaskName,
                LeaderUnitCode,
                data = r.Select(it => new
                {
                    it.SuperviseID,
                    it.LightSpot,
                    it.Problem,
                    Photos = !string.IsNullOrWhiteSpace(it.Photos) ? JArray.Parse(it.Photos) : new JArray(),
                    Status = it.Status.ToString(),
                    AssignLimit = it.Instime.AddDays(7).ToString("yyyy-MM-dd")
                })
            });
        }

        [User]
        public IActionResult GetSupervise()
        {
            var SuperviseID = Form.GetInt("SuperviseID");
            var supervise = ctx.Yqd_ExactSupervise.FirstOrDefault(it => it.SuperviseID == SuperviseID);
            if (supervise == null)
            {
                return this.ToJson(new
                {
                    success = true
                });
            }
            var TaskName = "";
            var task = ctx.Yqd_Task.FirstOrDefault(it => it.TaskID == supervise.TaskID);
            if (task != null) TaskName = task.TaskName;
            return this.ToJson(new
            {
                success = true,
                SuperviseID,
                TaskID = supervise.TaskID.ToString(),
                TaskName,
                supervise.LightSpot,
                supervise.Problem,
                Photos = !string.IsNullOrWhiteSpace(supervise.Photos) ? JArray.Parse(supervise.Photos) : new JArray(),
                AssignLimit = supervise.Instime.AddDays(7).ToString("yyyy-MM-dd")
            });
        }

        [User]
        public IActionResult GetAssignList()
        {
            var TaskTypeID = Form.GetLong("TaskTypeID");
            var Status = Form.GetInt("Status", 0);
            var isLb = Form.GetString("isLb");
            var year = Form.GetInt("Year", 0);
            var TaskType = Form.GetString("TaskType", "");
            var p = Form.GetInt("platform", 0);
            var LeaderUnitCode = Form.GetString("LeaderUnitCode", p == 2 ? WxUser.LogonInfo.UnitCode : "");
            var Leader = Form.GetString("Leader", "王健");
            var AreaCode = Form.GetString("AreaCode");
            var key = Form.GetString("key");
            var cp = Form.GetInt("page", 1);
            var size = Form.GetInt("pagesize", 10);
            var EvaluateInfo = Form.GetString("EvaluateInfo");
            var Link = Form.GetString("Link");
            var State = Form.GetInt("State", 0);
            if (cp < 1)
            {
                cp = 1;
            }
            var where = "  [taskID] in(select TaskID from Yqd_Task where IsShow =1 ";
            if (Status > 0)
            {
                where += " and [TaskStatus]=" + Status;
            }
            if (year > 0)
            {
                where += " and [Year]=" + year;
            }
            if (TaskType != "")
            {
                where += " and (','+[TaskType]+',') like '%," + TaskType + ",%'";
            }
            if (TaskTypeID > 0)
            {
                var TaskTypeIDs = ctx.Yqd_TaskType.Where(it => it.ParentID == TaskTypeID).Select(it => it.TypeID).ToArray();
                var sql = "";
                where += " and (";

                foreach (var id in TaskTypeIDs)
                {
                    sql += (string.IsNullOrEmpty(sql) ? "" : " or ") + "  TaskIDPath like '%" + id + "%' ";

                }
                where += sql;
                where += " )";
            }
            if (!string.IsNullOrWhiteSpace(LeaderUnitCode))
            {
                where += " and [LeaderUnitCode]='" + LeaderUnitCode + "'";
            }

            if (!string.IsNullOrWhiteSpace(AreaCode))
            {
                where += " and [FAreaCode]='" + AreaCode + "'";
            }
            if (State == 100)
            {
                where += " and [TaskID] in (select TaskID from Yqd_ExactSupervise where IsDel=0 and IsLb=0 and TaskID not in(select TaskID from Yqd_ExactSupervise where IsLb=0 and Status!=1))";
            }

            if (key.Trim() != "")
            {
                where += " and lower(ltrim(rtrim([TaskName]))) like '%" + key.ToLower().Trim() + "%'";
            }
            if (Leader != "王健")
            {
                where += " and [LeaderUnitCode] in (Select UnitCode From Yqd_Unit Where Leader='" + Leader + "' ) ";
            }
            if (EvaluateInfo.Trim() != "" && Status == 3)
            {
                var einfos = ctx.Yqd_EvaluateInfo.Where(it => it.TaskStatus == 3 && it.DataTable == EvaluateInfo && it.IsShow).Select(it => it.TaskID).ToList();
                if (einfos.Count > 0)
                {
                    where += " and [TaskID] in (" + string.Join(",", einfos) + ") ";
                }

            }
            if (Link.Trim() != "")
            {
                if (Link == "Yqd_QuotaTable")
                {
                    where += " and [TaskID] in (Select TaskID From Yqd_QuotaTable Where TState = 1 ) ";

                }
                else if (Link == "Yqd_QuotaMsg")
                {
                    where += " and [TaskID] in (Select TaskID From Yqd_QuotaMsg Where IsShow = 1 ) ";
                }
                else if (Link == "Yqd_Supervise_10")
                {
                    where += " and [TaskID] in (Select TaskID From Yqd_Supervise Where  isdel=0 and SuperviseType = 10 ) ";
                }
                else if (Link == "Yqd_Supervise_20")
                {
                    where += " and [TaskID] in (Select TaskID From Yqd_Supervise Where  isdel=0 and SuperviseType = 20 ) ";
                }
                else if (Link == "Yqd_QuotaPlan")
                {
                    where += " and [TaskID] in (Select TaskID From Yqd_QuotaTable Where TState = 2 ) ";
                }
                else
                {
                    where += " and [TaskID] in (Select TaskID From  " + Link + "  ) ";
                }

            }
            string whereStr = string.Empty;
            if (!string.IsNullOrEmpty(isLb))
            {
                whereStr += $" and islb={isLb}";
            }
            where += ") and [TaskId] not in(select TaskId from Yqd_Assign where ItemType = " + (int)事项类型.精准督查 + "  and ItemID=Yqd_ExactSupervise.SuperviseID) and IsDel=0 and status !=5 " + whereStr;

            //分组获取部门
            //var listUnit = ctx.Database.SqlQuery<Yqd_ExactSupervise>("select * From Yqd_ExactSupervise where " + where).GroupBy(x => x.UnitCode).Select(x => x.Key.ToString()).ToList();

            var (totalcount, data) = ctx.Database.SqlPager<Yqd_ExactSupervise>((cp - 1) * size, size, "*", where, "[Instime] desc, SuperviseID desc");
            var taskid = data.Select(it => it.TaskID).ToList();
            var ys = ctx.Yqd_Supervise.Where(it => it.IsDel == 0 && taskid.Contains(it.TaskID)).ToList();
            var dc = ctx.Yqd_ExactSupervise.Where(it => taskid.Contains(it.TaskID)).ToList();
            var ds = new List<object>();
            var task = ctx.Yqd_Task.Select(it => new { it.TaskID, it.TaskName, it.TaskPath, it.FAreaCode, it.UpdateTime, it.CreateTime, it.TaskStatus, it.LeaderUnitName, it.LeaderUnitCode }).ToList();

            var listUnit = new List<string>();
            foreach (var d in data)
            {
                var t = task.FirstOrDefault(it => it.TaskID == d.TaskID);
                if (t != null)
                {
                    ds.Add(new
                    {
                        d.SuperviseID,
                        TaskID = d.TaskID.ToString(),
                        t.TaskName,
                        t.FAreaCode,
                        d.Problem,
                        UpdateTime = t.UpdateTime.ToString("yyyy-MM-dd"),
                        CreateTime = t.CreateTime.ToString("yyyy-MM-dd"),
                        TaskPath = t.TaskPath.Split("/")[0],
                        isDB = ys.Any(it => it.TaskID == t.TaskID && it.SuperviseType == 督导类型.钉办),
                        isDD = ys.Any(it => it.TaskID == t.TaskID && it.SuperviseType == 督导类型.督导),
                        isDC = dc.Any(it => it.TaskID == t.TaskID),
                        t.LeaderUnitName,
                        t.LeaderUnitCode,
                        t.TaskStatus
                    });
                    listUnit.Add(t.LeaderUnitCode);
                }
            }
            return this.ToJson(new
            {
                success = true,
                totalcount,
                unitList = listUnit.Any() ? ctx.Yqd_Unit.Where(it => listUnit.Contains(it.UnitCode)).ToList() : new List<Yqd_Unit>(),
                data = ds
            });
        }
    }
}